using System;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    /**
     * Classname : HocVien
     * Version information :1.0
     * Date  :26-09-2006
     * Copyright notice : 
     */

    public class DAHocVienLV6LV8: DbObject
    {
        protected SqlConnection connec;

        public DAHocVienLV6LV8(SqlConnection Connection)
        {
            connec = Connection;
        }

        // return all the HocViens select Language
        // Danh cho nguoi quan tri
        public int loadDemSoHocVienLV6LV8ByTrangThai(int LevelID, int TrangThaiID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TrangThaiID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = LevelID;
            parameters[1].Value = TrangThaiID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVienLV6LV8_loadDemSoHocVienLV6LV8ByTrangThai", parameters, "TB_L2", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        // return all the HocViens select Language
        // Danh cho nguoi quan tri
        public DataSet loadHocVienLV6LV8()
        {
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVienLV6LV8_loadLV6LV8", "TB_L6_8", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }

        // return only the record with the specified ID!!!!!!!!!!!
        public DataRow loadHocVienLV6LV8ID(int HocVienLV6LV8Id, int LevelID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@HocVienID", SqlDbType.Int, 4),
                                             new SqlParameter("@LevelID", SqlDbType.Int, 4)                                        
                                        };
            parameters[0].Value = HocVienLV6LV8Id;
            parameters[1].Value = LevelID;

            using (DataSet HocViens = RunProcedure("sp_HocVienLV6LV8_loadLV6LV8ID", parameters, "TB_L6_8", connec))
            {
                return HocViens.Tables[0].Rows[0];
            }
        }
   
        // update the properties of the specified HocViens
        // Sua noi dung ban ghi hien hanh
        public int updateHocVienLV6LV8(int ID, int HocVienID, string DenThi, string BaiThi, string CoDiem, string GiayBaoTrungTuyen,
                    string NhanDuocGiayBaoTrungTuyen, string NopHocPhi, string NopHoSo ,int TrangThaiID, string GhiChu,
                        int LevelID, int NguoiDungID, DateTime NgayGoi, string DuKienNhapHoc, string ThucNhapHoc)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { 	    new SqlParameter("@HocVienID", SqlDbType.Int, 4),
                                                new SqlParameter("@DenThi", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@BaiThi", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@CoDiem", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GiayBaoTrungTuyen", SqlDbType.NVarChar,256),
                                                new SqlParameter("@NhanDuocGiayBaoTrungTuyen", SqlDbType.NVarChar,256),
                                                new SqlParameter("@NopHocPhi", SqlDbType.NVarChar,256),
                                                new SqlParameter("@NopHoSo", SqlDbType.NVarChar,256),                                             
                                                new SqlParameter("@TrangThaiID", SqlDbType.Int, 4),
                                                new SqlParameter("@GhiChu", SqlDbType.NVarChar,1024),
                                                new SqlParameter("@LevelID", SqlDbType.Int, 4),
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int ,4),
                                                new SqlParameter("@NgayGoi", SqlDbType.DateTime, 8),
                                                new SqlParameter("@ID", SqlDbType.Int ,4),
                                                new SqlParameter("@DuKienNhapHoc", SqlDbType.NVarChar, 512),
                                                new SqlParameter("@ThucNhapHoc", SqlDbType.NVarChar, 512)                                                
										};
            // set the values
            parameters[0].Value = HocVienID;
            parameters[1].Value = DenThi;
            parameters[2].Value = BaiThi.Trim();
            parameters[3].Value = CoDiem.Trim();
            parameters[4].Value = GiayBaoTrungTuyen;
            parameters[5].Value = NhanDuocGiayBaoTrungTuyen.Trim();
            parameters[6].Value = NopHocPhi.Trim();
            parameters[7].Value = NopHoSo.Trim();
            parameters[8].Value = TrangThaiID;
            parameters[9].Value = LevelID;
            parameters[10].Value = NguoiDungID;
            parameters[11].Value = NgayGoi;
            parameters[12].Value = ID;
            parameters[13].Value = DuKienNhapHoc;
            parameters[14].Value = ThucNhapHoc;
            // run the procedure
            RunProcedure("sp_HocVienLV6LV8_UpdateLV6LV8beta", parameters, out numAffected, connec);

            return numAffected;
        }

        // add a new question
        // Them ban ghi hien hanh
        public int insertHocVienLV6LV8(int HocVienID, string DenThi, string BaiThi, string CoDiem, string GiayBaoTrungTuyen,
                    string NhanDuocGiayBaoTrungTuyen, string NopHocPhi, string NopHoSo, int TrangThaiID, string GhiChu,
                            int LevelID, int NguoiDungID, DateTime NgayGoi, string NoiDungNgayGoi, string NoiDungTaiSao,
                        int intTrangThaiGoiID, int DiemL6_8, string DuKienNhapHoc, string ThucNhapHoc)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	   
                                                new SqlParameter("@ID", SqlDbType.Int ,4),
                                                new SqlParameter("@HocVienID", SqlDbType.Int, 4),
                                                new SqlParameter("@DenThi", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@BaiThi", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@CoDiem", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GiayBaoTrungTuyen", SqlDbType.NVarChar,256),
                                                new SqlParameter("@NhanDuocGiayBaoTrungTuyen", SqlDbType.NVarChar,256),
                                                new SqlParameter("@NopHocPhi", SqlDbType.NVarChar,256),
                                                new SqlParameter("@NopHoSo", SqlDbType.NVarChar,256),                                             
                                                new SqlParameter("@TrangThaiID", SqlDbType.Int, 4),
                                                new SqlParameter("@GhiChu", SqlDbType.NVarChar,1024),
                                                new SqlParameter("@LevelID", SqlDbType.Int, 4),
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int ,4),
                                                new SqlParameter("@NgayGoi", SqlDbType.DateTime,8),	
                                                new SqlParameter("@NoiDungNgayGoi", SqlDbType.NVarChar, 2000),	
                                                new SqlParameter("@NoiDungTaiSao", SqlDbType.NVarChar, 2000),
                                                new SqlParameter("@TrangThaiGoiID", SqlDbType.Int ,4),
                                                new SqlParameter("@Diem", SqlDbType.Int ,4),
                                                new SqlParameter("@DuKienNhapHoc", SqlDbType.NVarChar, 512),
                                                new SqlParameter("@ThucNhapHoc", SqlDbType.NVarChar, 512)
										 };
            // set the values
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = HocVienID;
            parameters[2].Value = DenThi;
            parameters[3].Value = BaiThi.Trim();
            parameters[4].Value = CoDiem.Trim();
            parameters[5].Value = GiayBaoTrungTuyen;
            parameters[6].Value = NhanDuocGiayBaoTrungTuyen.Trim();
            parameters[7].Value = NopHocPhi.Trim();
            parameters[8].Value = NopHoSo.Trim();
            parameters[9].Value = TrangThaiID;
            parameters[10].Value = GhiChu;
            parameters[11].Value = LevelID;
            parameters[12].Value = NguoiDungID;
            parameters[13].Value = NgayGoi;
            parameters[14].Value = NoiDungNgayGoi;
            parameters[15].Value = NoiDungTaiSao;
            parameters[16].Value = intTrangThaiGoiID;
            parameters[17].Value = DiemL6_8;
            parameters[18].Value = DuKienNhapHoc;
            parameters[19].Value = ThucNhapHoc;
            // run the procedure
            RunProcedure("sp_HocVienLV6LV8_insertLV6LV8Mainbeta", parameters, out numAffected, connec);

            //return (int)parameters[0].Value;
            return numAffected;
        }

        // add a new question
        // Them ban ghi hien hanh
        public int updateHocVienLV6LV8(int HocVienID, string DuKienNhapHoc, string ThucNhapHoc)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	   
                                                new SqlParameter("@ID", SqlDbType.Int ,4),
                                                new SqlParameter("@HocVienID", SqlDbType.Int, 4),
                                                new SqlParameter("@DuKienNhapHoc", SqlDbType.NVarChar, 512),
                                                new SqlParameter("@ThucNhapHoc", SqlDbType.NVarChar, 512)
										 };
            // set the values
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = HocVienID;
            parameters[2].Value = DuKienNhapHoc;
            parameters[3].Value = ThucNhapHoc;
            // run the procedure
            RunProcedure("sp_HocVienLV6LV8_UpdatetLV6LV8Mainbeta", parameters, out numAffected, connec);

            //return (int)parameters[0].Value;
            return numAffected;
        }
        // Xoa Role
        public int removeHocVienLV6LV8(int HocVienID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int) };
            parameters[0].Value = HocVienID;
            // run the procedure
            RunProcedure("sp_HocVienLV6LV8_removeLV6LV8", parameters, out numAffected, connec);
            return numAffected;
        }
    }
}